from datetime import datetime, date
from uuid import uuid4

from sqlalchemy import Integer, CHAR, Boolean, Numeric, Text, TIMESTAMP, func, DECIMAL, Date, Index, text, SmallInteger, \
    DateTime, UniqueConstraint, ForeignKey, Enum, ForeignKeyConstraint, PrimaryKeyConstraint
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from typing import Optional, List

from app.db.model.base import Base


# def get_class_by_tablename(tablename: str):
#     """通过表名获取对应的 ORM 类"""
#     # 获取所有已注册的类
#     registry = Base.registry
#
#     for mapper in registry.mappers:
#         class_ = mapper.class_
#         if hasattr(class_, '__tablename__') and class_.__tablename__ == tablename:
#             return class_
#     raise LookupError(f"表 {tablename} 未注册")


# class UserInfo(Base):
#     __tablename__ = "user_info"
#     __table_args__ = {
#         'comment': '用户信息表'
#     }
#
#     id: Mapped[str] = mapped_column(
#         CHAR(36),
#         primary_key=True,
#         comment="主键",
#         default=lambda: str(uuid4())
#     )
#     user_id: Mapped[str] = mapped_column(
#         String(50),
#         nullable=False,
#         comment="ID"
#     )
#     password: Mapped[str] = mapped_column(
#         String(255),
#         nullable=False,
#         comment="密码"
#     )
#     name: Mapped[Optional[str]] = mapped_column(
#         String(50),
#         nullable=True,
#         comment="姓名"
#     )
#     role: Mapped[Optional[str]] = mapped_column(
#         String(255),
#         nullable=True,
#         comment="角色"
#     )
#
#     @classmethod
#     def allow_fields(cls, user_role=None):
#         """定义前端可访问字段"""
#         base_fields = ["user_id", "name", "role"]
#
#         if user_role == "admin":
#             return base_fields + ["id"]
#
#         return base_fields


class CourseSchedule(Base):
    __tablename__ = "course_schedule"
    __table_args__ = (
        {
            "comment": "课程安排表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    id1: Mapped[int] = mapped_column(
        Integer,
        nullable=True,
        comment="子序号"
    )
    grade: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="年级"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="班级名称"
    )
    student_amount: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="学生人数"
    )
    major: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="专业(代码)"
    )
    enrollment_method: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="升学方式"
    )
    parner_institution: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="对接院校"
    )
    course_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="课程代码"
    )
    course_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="课程名称"
    )
    course_type: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="课程类型"
    )
    weekly_hour: Mapped[Optional[Numeric]] = mapped_column(
        DECIMAL(5, 2),
        nullable=True,
        comment="周学时"
    )
    teaching_week: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="教学周数"
    )
    assessment_method: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="考核方式"
    )
    book_department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教材征订系部"
    )
    course_department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="课程所属系部"
    )
    instructor: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="任课教师"
    )
    class_advisor: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="班主任"
    )
    campus: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="校区"
    )
    schedule1: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周一课表"
    )
    schedule2: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周二课表"
    )
    schedule3: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周三课表"
    )
    schedule4: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周四课表"
    )
    schedule5: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周五课表"
    )
    schedule6: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周六课表"
    )
    schedule7: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="周日课表"
    )
    remark1: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备注1"
    )
    book_name: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="教材名称"
    )
    first_editor_in_chief: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="第一主编"
    )
    publishing_unit: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="出版单位"
    )
    isbn: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="ISBN号"
    )
    release: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="版次"
    )
    release_date: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="版次日期"
    )
    book_nature: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教材性质"
    )
    book_source: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教材来源"
    )
    book_type: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教材类型"
    )
    price: Mapped[Optional[Numeric]] = mapped_column(
        DECIMAL(10, 2),
        nullable=True,
        comment="价格"
    )
    course_nature: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="课程性质"
    )
    is_select_first: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="是否首次选用"
    )
    select_first_date: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="首次选用时间"
    )
    used_year: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="已使用年限"
    )
    remark2: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备注2"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        base_fields = ["semester", "id1", "grade", "class_name", "student_amount", "major", "enrollment_method",
                       "parner_institution", "course_id", "course_name", "course_type", "weekly_hour", "teaching_week",
                       "assessment_method", "book_department", "course_department", "instructor", "class_advisor",
                       "campus", "schedule1", "schedule2", "schedule3", "schedule4", "schedule5", "schedule6",
                       "schedule7", "remark1", "book_name", "first_editor_in_chief", "publishing_unit", "isbn",
                       "release", "release_date", "book_nature", "book_source", "book_type", "price", "course_nature",
                       "is_select_first", "select_first_date", "used_year", "remark2"
                       ]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


class ClassInfo(Base):
    __tablename__ = "class_info"
    __table_args__ = (
        {
            "comment": "班级数据表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_row_format": "DYNAMIC"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        comment="序号"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="系部"
    )
    major: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="专业"
    )
    grade: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="年级"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="班级"
    )
    student_amount: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="学生人数"
    )
    parner_institution: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="对接院校"
    )
    class_advisor: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="班主任"
    )
    campus: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="校区"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["department", "major", "grade", "class_name", "student_amount", "parner_institution",
                       "class_advisor", "campus"]

        if user_role == "admin":
            return base_fields + ["id"]

        return base_fields


class StudentScore(Base):
    __tablename__ = "student_score"
    __table_args__ = (
        {
            "comment": "学生成绩表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="主键ID"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="学期"
    )
    course: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="课程"
    )
    teacher: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教师"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="班级"
    )
    student_id: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="学号"
    )
    student_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="姓名"
    )
    student_status: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        server_default="正常",
        comment="状态"
    )
    score_daily: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="平时成绩"
    )
    score_exam: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="考试成绩"
    )
    score_task: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="作业成绩"
    )
    score_test: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="小测成绩"
    )
    score_final: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="最终成绩"
    )
    score_make_up: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        name="score_make-up",  # 特殊字段名需要指定
        comment="补考成绩"
    )
    exam_status: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        server_default="正常",
        comment="考试状态"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "semester",
            "course",
            "teacher",
            "class_name",
            "student_id",
            "student_name",
            "student_status",
            "score_daily",
            "score_exam",
            "score_task",
            "score_test",
            "score_final",
            "score_make_up",
            "exam_status",
        ]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


class ConsumablesManagement(Base):
    __tablename__ = "consumables_management"
    __table_args__ = (
        {
            "comment": "耗材管理表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
        }
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教学系部"
    )
    consumable_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="耗材名称"
    )
    specification_model: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="规格型号"
    )
    technical_parameters: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="技术参数"
    )
    manufacturer_brand: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="厂家品牌"
    )
    amount: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="数量"
    )
    unit: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="单位"
    )
    unit_price: Mapped[Optional[Numeric]] = mapped_column(
        Numeric(10, 2),
        nullable=True,
        comment="单价"
    )
    total_price: Mapped[Optional[Numeric]] = mapped_column(
        Numeric(10, 2),
        nullable=True,
        comment="合计"
    )
    teacher_responsible: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="系部负责老师"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备注"
    )
    purchase_link: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="耗材购买联系方式或链接"
    )
    storage_location: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="存放地点"
    )
    manager_1: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="管理人员"
    )
    date_used: Mapped[Optional[date]] = mapped_column(
        String(50),
        nullable=True,
        comment="使用日期"
    )
    amount_used: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        server_default="0",
        comment="使用数量"
    )
    class_used: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="使用班级"
    )
    teacher_course: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="任课教师"
    )
    student_representative: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学生代表"
    )
    amount_remaining: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="剩余数量"
    )
    manager_2: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="管理人员(复核)"
    )
    created_at: Mapped[Optional[datetime]] = mapped_column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        comment="创建时间"
    )
    updated_at: Mapped[Optional[datetime]] = mapped_column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "semester", "department", "consumable_name", "specification_model", "technical_parameters",
                       "manufacturer_brand", "amount", "unit", "unit_price", "total_price", "teacher_responsible",
                       "remark", "purchase_link", "storage_location", "manager_1", "date_used", "amount_used",
                       "class_used", "teacher_course", "student_representative", "amount_remaining", "manager_2"
                       ]
        if user_role == "admin":
            return base_fields + [
                "created_at", "created_at"
            ]
        return base_fields


class BuildingDetail(Base):
    __tablename__ = "building_detail"
    __table_args__ = (
        {
            "comment": "场室详细信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_row_format": "DYNAMIC",
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    building_name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="楼栋名称"
    )
    floor: Mapped[str] = mapped_column(
        String(10),
        nullable=False,
        comment="楼层"
    )
    room_number: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="房间号"
    )
    doorplate: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        comment="门牌名称"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="负责部门"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备注"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        base_fields = ["building_name", "floor", "room_number", "doorplate", "department", "remark"]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


# class DepartmentInfo(Base):
#     __tablename__ = "department_info"
#     __table_args__ = (
#         {
#             "comment": "部门信息表",
#             "mysql_engine": "InnoDB",
#             "mysql_charset": "utf8mb4",
#             "mysql_collate": "utf8mb4_0900_ai_ci",
#             "mysql_row_format": "DYNAMIC"
#         },
#     )
#
#     id: Mapped[int] = mapped_column(
#         Integer,
#         primary_key=True,
#         autoincrement=True,
#         comment="ID"
#     )
#     name: Mapped[str] = mapped_column(
#         String(20),
#         nullable=False,
#         comment="部门全称"
#     )
#     abbr: Mapped[Optional[str]] = mapped_column(
#         String(20),
#         nullable=True,
#         comment="部门简称"
#     )
#
#     @classmethod
#     def allow_fields(cls, user_role=None):
#         """定义前端可访问字段"""
#         base_fields = [
#             "id",
#             "name",
#             "abbr"
#         ]
#         return base_fields


class PreparationGroup(Base):
    __tablename__ = "preparation_group"
    __table_args__ = (
        {
            "comment": "备课组人员原始表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 423  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教学系部"
    )
    tgp_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备课组名称"
    )
    course: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="课程"
    )
    pgp_leader: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="备课组长"
    )
    pgp_member: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备课小组成员"
    )
    pgp_schedule: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="备课时间及地点"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        base_fields = ["semester", "department", "tgp_name", "course", "pgp_leader", "pgp_member", "pgp_schedule"]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


class PreparationGroupDerive(Base):
    __tablename__ = "preparation_group_derive"
    __table_args__ = (
        {
            "comment": "备课组成员信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_row_format": "DYNAMIC",
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="主键，自增"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="部门"
    )
    teacher_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教师姓名"
    )
    teacher_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教师ID"
    )
    pgp_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备课组名字"
    )
    pgp_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="备课组ID"
    )
    course: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="课程"
    )
    pgp_schedule: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备课时间地点"
    )
    is_pgp_leader: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="0",
        comment="是否备课组长"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备注"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        base_fields = ["semester", "department", "teacher_name", "teacher_id", "pgp_name", "pgp_id", "course",
                       "pgp_schedule", "is_pgp_leader", "remark"]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


class TeachingGroup(Base):
    __tablename__ = "teaching_group"
    __table_args__ = (
        {
            "comment": "教研组人员原始表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 195  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教学系部"
    )
    tgp_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教研组名称"
    )
    tgp_leader: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教研组长"
    )
    teacher_full_time: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="在编专任教师"
    )
    teacher_administrative: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="行政兼课教师"
    )
    teacher_full_time_non: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="未兼课系部教师"
    )
    teacher_administrative_non: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="未兼课行政教师"
    )
    teacher_substitute: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="代课教师"
    )
    amount: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="人数(不含代课、未兼课教师)"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        base_fields = ["semester", "department", "tgp_name", "course", "pgp_leader", "pgp_member", "pgp_schedule"]
        if user_role == "admin":
            return base_fields + ["id"]
        return base_fields


from sqlalchemy import String, Integer, Text
from sqlalchemy.orm import Mapped, mapped_column
from typing import Optional, List

class TeachingGroupInfo(Base):
    __tablename__ = "teaching_group_info"
    __table_args__ = (
        # 表级配置
        {
            "comment": "教研组人员数据原始总表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_row_format": "DYNAMIC"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="教学系部"
    )
    tgp_name: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="教研组名称"
    )
    tgp_leader: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="教研组长"
    )
    teacher_full_time: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="在编专任教师"
    )
    teacher_administrative: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="行政兼课教师(系部主任除外)"
    )
    teacher_full_time_non: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="未兼课系部教师"
    )
    teacher_administrative_non: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="未兼课行政教师"
    )
    teacher_substitute: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="代课教师"
    )
    amount: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="人数(不含代课、未兼课教师)"
    )

    # 定义关系（如果需要的话）
    # 例如，如果与其他表有关联，可以在这里添加relationship

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "semester",
            "department",
            "tgp_name",
            "tgp_leader",
            "teacher_full_time",
            "teacher_administrative",
            "teacher_full_time_non",
            "teacher_administrative_non",
            "teacher_substitute",
            "amount"
        ]

        if user_role == "admin":
            return base_fields + [
                "id"
            ]

        return base_fields

    def __repr__(self):
        return f"<TeachingGroupInfo(id={self.id}, semester='{self.semester}', department='{self.department}', tgp_name='{self.tgp_name}')>"


class TeachingGroupDerive(Base):
    __tablename__ = "teaching_group_derive"
    __table_args__ = (
        {
            "comment": "教研组人员信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_row_format": "DYNAMIC",
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="部门"
    )
    teacher_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教师姓名"
    )
    teacher_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教师ID"
    )
    tgp_name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教研组名称"
    )
    tgp_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="教研组ID"
    )
    teacher_type: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="教师类型"
    )
    is_tgp_leader: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="0",
        comment="是否教研组长"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备注"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["semester", "department", "teacher_name", "teacher_id", "tgp_name", "tgp_id",
                       "teacher_type", "is_tgp_leader", "remark"]
        if user_role == "admin":
            return base_fields + [
                "id"
            ]
        return base_fields


class TeachingMaterial(Base):
    __tablename__ = "teaching_material"
    __table_args__ = (
        {
            "comment": "教学资料",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        comment="序号"
    )
    material_type: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="资料类型"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学期/类型"
    )
    department: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="教学系部"
    )
    file_name: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="文件名"
    )
    course: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="课程名称"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="授课班级"
    )
    teacher: Mapped[Optional[str]] = mapped_column(
        String(200),
        nullable=True,
        comment="授课教师"
    )
    file_path: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="文件路径"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "material_type",
            "semester",
            "department",
            "file_name",
            "course",
            "class_name",
            "teacher",
            "file_path",
        ]
        if user_role == "admin":
            return base_fields + [
                "id"
            ]
        return base_fields


class ConsumableApplyRefreshToken(Base):
    """
        Refresh Token存储表
        字段说明:
        - jti: JWT唯一标识(主键)
        - user_id: 关联的用户ID
        - token_hash: token的加密哈希值
        - expires: 过期时间
        - revoked: 是否已主动撤销
        - client_fp: 客户端指纹（防止token盗用）
        """
    __tablename__ = 'consumable_apply_refresh_tokens'
    __table_args__ = (
        Index('idx_user_id', 'user_id'),
        Index('idx_composite', 'user_id', 'expires', 'revoked'),
        {'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci'}
    )

    jti: Mapped[str] = mapped_column(
        String(36),
        primary_key=True,
        default=lambda: str(uuid4()),
        comment='JWT唯一标识'
    )
    user_id: Mapped[str] = mapped_column(
        String(36),
        nullable=False,
        comment='关联用户UUID'
    )
    token_hash: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        comment='令牌哈希值'
    )
    expires: Mapped[datetime] = mapped_column(
        nullable=False,
        comment='过期时间'
    )
    revoked: Mapped[int] = mapped_column(
        default=0,
        nullable=False,
        comment='是否撤销(0:否 1:是)'
    )
    client_fp: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        comment='客户端指纹哈希'
    )
    created_at: Mapped[datetime] = mapped_column(
        server_default=text('CURRENT_TIMESTAMP'),
        nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
        nullable=False
    )

    def is_valid(self):
        """验证token有效性：未撤销且未过期"""
        return not self.revoked and datetime.utcnow() < self.expires

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = []
        if user_role == "admin":
            return base_fields + [
                "jti",
                "user_id",
                "token_hash",
                "expires",
                "revoked",
                "client_fp",
                "created_at",
                "updated_at",
            ]
        return base_fields


class ConsumableUsage(Base):
    __tablename__ = "consumable_usage"
    __table_args__ = (
        Index('idx_consumable_semester', 'consumable_id', 'semester'),
        {
            "comment": "耗材领用记录表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="领用记录ID"
    )
    consumable_id: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        comment="耗材总表ID（记录consumables_management.id）"
    )
    semester: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="学期"
    )
    consumable_name: Mapped[str] = mapped_column(
        String(100),
        nullable=False,
        comment="耗材名称"
    )
    department: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="教学系部"
    )
    amount_used: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        comment="本次领用数量"
    )
    class_used: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="使用班级"
    )
    student_representative: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="学生代表"
    )
    date_used: Mapped[date] = mapped_column(
        Date,
        nullable=False,
        comment="领用日期"
    )
    status: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=False,
        comment="审批状态"
    )
    applicant_id: Mapped[Optional[str]] = mapped_column(String(50), nullable=False, comment='申请人ID')
    applicant_name: Mapped[Optional[str]] = mapped_column(String(50), nullable=False, comment='申请人姓名')
    application_id: Mapped[str] = mapped_column(
        CHAR(36),
        primary_key=True,
        comment="申请编号",
        default=lambda: str(uuid4())
    )
    created_at: Mapped[Optional[TIMESTAMP]] = mapped_column(
        TIMESTAMP,
        nullable=True,
        server_default="CURRENT_TIMESTAMP",
        comment="创建时间"
    )
    updated_at: Mapped[Optional[TIMESTAMP]] = mapped_column(
        TIMESTAMP,
        nullable=True,
        server_default="CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        comment="更新时间"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "consumable_id",
            "semester",
            "consumable_name",
            "department",
            "amount_used",
            "class_used",
            "student_representative",
            "date_used",
            "status",
            "applicant_id",
            "applicant_name",
            "application_id",
        ]
        if user_role == "admin":
            return base_fields + ["id", "created_at", "updated_at"]
        return base_fields


class TeachingMaterialSubmit(Base):
    __tablename__ = "teaching_material_submit"
    __table_args__ = (
        {
            "comment": "教学资料提交表\n\n提交规则：\n年级 包含 \"高职\" ，不用提交资料\n任课教师=空，不用提交资料\n课程所属系部=\"教务科\"、\"学生科\"，不用提交资料\n课程所属系部 包含 \"公共基础\"：不用提交 实训计划\n课程所属系部 = 各专业系部时，如果课程类型是A类，不用提交实训计划；如果课程类型是B类，提交全部资料；如果课程类型是C类，不用提交资料\n\n审核状态3种：未审0（黄），审过1（绿），审不过2（红）",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    material_id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="资料id"
    )
    file_path: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="资料路径"
    )
    material_type: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="资料类型"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="学期"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="班级名称"
    )
    course_name: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="课程名称"
    )
    course_department: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="课程所属系部"
    )
    teacher_name: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="任课教师"
    )
    week: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="教案教学周"
    )
    submit_time: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="提交时间"
    )
    tgp_check: Mapped[bool] = mapped_column(
        Boolean,
        default=False,
        comment="教研组长审核"
    )
    tgp_check_desc: Mapped[Optional[str]] = mapped_column(
        String(255),
        default="",
        comment="教师组审核描述"
    )
    dept_check: Mapped[bool] = mapped_column(
        Boolean,
        default=False,
        comment="部长审核"
    )
    dept_check_desc: Mapped[Optional[str]] = mapped_column(
        String(255),
        default="",
        comment="部门审核描述"
    )
    ai_check_result1: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="AI检查结论(反思检查)"
    )
    ai_check_result2: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="AI检查结论(内容检查)"
    )
    ai_check_detail2: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="AI检查细节(内容检查)"
    )
    ai_check_time2: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="AI检查时间(内容检查)"
    )
    ai_check_result3: Mapped[Optional[str]] = mapped_column(
        String(100),
        nullable=True,
        comment="AI检查结论(对照检查)"
    )
    ai_check_detail3: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="AI检查细节(对照检查)"
    )
    ai_check_time3: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="AI检查时间(对照检查)"
    )
    ai_check_time1: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="AI检查时间(反思检查)"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "file_path",
            "material_type",
            "semester",
            "class_name",
            "course_name",
            "course_department",
            "teacher_name",
            "week",
            "submit_time",
            "tgp_check",
            "tgp_check_desc",
            "dept_check",
            "dept_check_desc",
            "ai_check_result1",
            "ai_check_result2",
            "ai_check_detail2",
            "ai_check_time2",
            "ai_check_result3",
            "ai_check_detail3",
            "ai_check_time3",
            "ai_check_time1",
        ]
        if user_role == "admin":
            return base_fields + [
                "material_id"
            ]
        return base_fields


class TeachingScheduleWeek(Base):
    __tablename__ = "teaching_schedule_week"
    __table_args__ = (
        UniqueConstraint('semester', name='uk_semester',
                         comment='确保一个学期只有一条记录'),
        Index('idx_semester', 'semester'),
        {
            "comment": "教学周安排表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        comment="序号"
    )
    semester: Mapped[str] = mapped_column(
        String(20),
        nullable=True,
        comment="学期"
    )
    week01: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第01周日期段"
    )
    week02: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第02周日期段"
    )
    week03: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第03周日期段"
    )
    week04: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第04周日期段"
    )
    week05: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第05周日期段"
    )
    week06: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第06周日期段"
    )
    week07: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第07周日期段"
    )
    week08: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第08周日期段"
    )
    week09: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第09周日期段"
    )
    week10: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第10周日期段"
    )
    week11: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第11周日期段"
    )
    week12: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第12周日期段"
    )
    week13: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第13周日期段"
    )
    week14: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第14周日期段"
    )
    week15: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第15周日期段"
    )
    week16: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第16周日期段"
    )
    week17: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第17周日期段"
    )
    week18: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第18周日期段"
    )
    week19: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第19周日期段"
    )
    week20: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第20周日期段"
    )
    week21: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第21周日期段"
    )
    week22: Mapped[Optional[str]] = mapped_column(
        String(30),
        nullable=True,
        comment="第22周日期段"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="学期说明"
    )

    # 如果需要关联其他表，可以在这里添加关系定义
    # 例如关联学期表
    # semester_rel: Mapped["Semester"] = relationship(
    #     "Semester",
    #     primaryjoin="foreign(TeachingScheduleWeek.semester) == Semester.name",
    #     viewonly=True
    # )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "id", "semester", "week01", "week02", "week03", "week04", "week05",
            "week06", "week07", "week08", "week09", "week10", "week11", "week12",
            "week13", "week14", "week15", "week16", "week17", "week18", "week19",
            "week20", "week21", "week22", "remark"
        ]

        return base_fields

    def get_week_date(self, week_num: int) -> Optional[str]:
        """获取指定周数的日期范围"""
        if 1 <= week_num <= 22:
            week_field = f"week{week_num:02d}"
            return getattr(self, week_field, None)
        return None

    def set_week_date(self, week_num: int, date_range: str) -> None:
        """设置指定周数的日期范围"""
        if 1 <= week_num <= 22:
            week_field = f"week{week_num:02d}"
            setattr(self, week_field, date_range)

    def to_dict(self, include_weeks=True):
        """转换为字典格式"""
        result = {
            "id": self.id,
            "semester": self.semester,
            "remark": self.remark
        }

        if include_weeks:
            for week_num in range(1, 23):
                week_field = f"week{week_num:02d}"
                result[week_field] = getattr(self, week_field, None)

        return result


class TeachingScheduleCourse(Base):
    __tablename__ = "teaching_schedule_course"
    __table_args__ = (
        UniqueConstraint('semester', 'id1', name='uk_semester_subid', comment='确保学期内子序号唯一'),
        Index('idx_semester_class', 'semester', 'class_name'),
        Index('idx_semester_major', 'semester', 'major'),
        {
            "comment": "教学进度表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        comment="序号"
    )
    semester: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="学期"
    )
    campus: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="校区"
    )
    id1: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="子序号"
    )
    grade: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="年级"
    )
    class_name: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="班级"
    )
    major: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="专业(代码)"
    )
    enrollment_method: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="升学方式"
    )
    week01: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第01周"
    )
    week02: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第02周"
    )
    week03: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第03周"
    )
    week04: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第04周"
    )
    week05: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第05周"
    )
    week06: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第06周"
    )
    week07: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第07周"
    )
    week08: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第08周"
    )
    week09: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第09周"
    )
    week10: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第10周"
    )
    week11: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第11周"
    )
    week12: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第12周"
    )
    week13: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第13周"
    )
    week14: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第14周"
    )
    week15: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第15周"
    )
    week16: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第16周"
    )
    week17: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第17周"
    )
    week18: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第18周"
    )
    week19: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第19周"
    )
    week20: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第20周"
    )
    week21: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第21周"
    )
    week22: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="第22周"
    )

    # 如果需要关联其他表，可以在这里添加关系定义
    # 例如关联学期表
    # semester_rel: Mapped["Semester"] = relationship(
    #     "Semester",
    #     primaryjoin="foreign(TeachingScheduleCourse.semester) == Semester.name",
    #     viewonly=True,
    #     back_populates="teaching_schedule_courses"
    # )

    # 如果需要关联班级表
    # class_info: Mapped["ClassInfo"] = relationship(
    #     "ClassInfo",
    #     primaryjoin="foreign(TeachingScheduleCourse.class_name) == ClassInfo.name",
    #     viewonly=True,
    #     back_populates="teaching_schedules"
    # )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "id", "semester", "campus", "id1", "grade", "class_name", "major",
            "enrollment_method"
        ]

        # 添加所有周数字段
        week_fields = [f"week{str(i).zfill(2)}" for i in range(1, 23)]
        base_fields.extend(week_fields)

        if user_role in ["admin", "teacher"]:
            return base_fields

        return base_fields

    def get_week_status(self, week_num: int) -> Optional[str]:
        """获取指定周数的状态"""
        if 1 <= week_num <= 22:
            week_field = f"week{week_num:02d}"
            return getattr(self, week_field, None)
        return None

    def set_week_status(self, week_num: int, status: str) -> None:
        """设置指定周数的状态"""
        if 1 <= week_num <= 22:
            week_field = f"week{week_num:02d}"
            setattr(self, week_field, status)

    def to_dict(self, include_weeks=True):
        """转换为字典格式"""
        result = {
            "id": self.id,
            "semester": self.semester,
            "campus": self.campus,
            "id1": self.id1,
            "grade": self.grade,
            "class_name": self.class_name,
            "major": self.major,
            "enrollment_method": self.enrollment_method
        }

        if include_weeks:
            for week_num in range(1, 23):
                week_field = f"week{week_num:02d}"
                result[week_field] = getattr(self, week_field, None)

        return result

    def get_teaching_weeks(self) -> List[int]:
        """获取有教学安排的周数列表"""
        teaching_weeks = []
        for week_num in range(1, 23):
            week_field = f"week{week_num:02d}"
            status = getattr(self, week_field, None)
            if status and status.strip():  # 如果有状态值且不为空
                teaching_weeks.append(week_num)
        return teaching_weeks


class CourseInfo(Base):
    __tablename__ = 'course_info'

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment='主键ID'
    )

    name: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='课程名称'
    )

    code: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='课程代码'
    )

    course_type: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='课程类型'
    )

    course_class: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='课程班级'
    )

    course_depart: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='开课部门'
    )

    course_hour: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='课程学时'
    )

    note: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment='备注'
    )

    def __repr__(self) -> str:
        return f"<CourseInfo(id={self.id}, name='{self.name}', code='{self.code}')>"


class UserInfo(Base):
    __tablename__ = "user_info"
    __table_args__ = (
        Index('idx_role', 'role'),
        {
            "comment": "用户账号表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    user_id: Mapped[str] = mapped_column(
        String(50),
        primary_key=True,
        comment="登录账号（工号/学号/临时编号）"
    )
    password: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        comment="加密密码"
    )
    role: Mapped[str] = mapped_column(
        Enum('student', 'teacher', 'admin', 'guest', 'dev_operator',
             name='user_role',
             create_constraint=True,
             validate_strings=True),
        nullable=False,
        comment="角色"
    )
    status: Mapped[Optional[str]] = mapped_column(
        Enum('active', 'locked',
             name='account_status',
             create_constraint=True,
             validate_strings=True),
        server_default="active",
        comment="账号状态"
    )
    last_login: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="最后登录时间"
    )
    created_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        server_default="CURRENT_TIMESTAMP",
        comment="创建时间"
    )
    updated_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        server_default="CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        comment="更新时间"
    )

    # 添加反向关系
    teacher_profile: Mapped[Optional["TeacherDetail"]] = relationship(
        "TeacherDetail",
        back_populates="user_account",
        uselist=False,
        cascade="all, delete-orphan"
    )
    other_info: Mapped[Optional["OtherUserInfo"]] = relationship(
        "OtherUserInfo",
        back_populates="user_account",
        uselist=False,
        cascade="all, delete-orphan"
    )
    system_roles: Mapped[List["UserSystemRole"]] = relationship(
        "UserSystemRole",
        back_populates="user",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["user_id", "name", "role", "status"]

        if user_role == "admin":
            return base_fields + ["last_login"]

        return base_fields


class TeacherDetail(Base):
    __tablename__ = "teacher_detail"
    __table_args__ = (
        # 唯一约束
        UniqueConstraint('user_id', name='uk_user_id', comment='确保user_id唯一'),
        UniqueConstraint('teacher_id', name='teacher_id'),

        # 索引
        Index('idx_teacher_id', 'teacher_id'),
        Index('idx_name', 'name'),

        # 外键约束
        ForeignKeyConstraint(
            ['user_id'],
            ['user_info.user_id'],
            ondelete='CASCADE',
            name='teacher_info_ibfk_1'
        ),

        # 表级配置
        {
            "comment": "教师信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },

    )

    teacher_id: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="工号（可为空，临时教师）"
    )
    user_id: Mapped[str] = mapped_column(
        String(50),
        primary_key=True,
        nullable=False,
        comment="关联 user_info.user_id"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="姓名"
    )
    sex: Mapped[Optional[str]] = mapped_column(
        String(2),
        nullable=True,
        comment="男/女"
    )
    archive_id: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="档案号"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备注"
    )

    # 定义与用户账号的关系
    user_account: Mapped["UserInfo"] = relationship(
        "UserInfo",
        back_populates="teacher_profile",
        foreign_keys=[user_id]
    )

    # 添加反向关系
    semester_data: Mapped[List["TeacherData"]] = relationship(
        "TeacherData",
        back_populates="teacher",
        cascade="all, delete-orphan"
    )
    tgp_members: Mapped[List["TGPMember"]] = relationship(
        "TGPMember",
        back_populates="teacher",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["teacher_id", "user_id", "name", "sex", "remark"]

        if user_role == "admin":
            return base_fields + ["archive_id"]

        return base_fields


class DepartmentInfo(Base):
    __tablename__ = "department_info"
    __table_args__ = (
        {
            "comment": "教学部门表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[str] = mapped_column(
        String(20),
        primary_key=True,
        comment="部门编码（拼音首字母大写）"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="部门名称"
    )
    abbr: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="部门简称"
    )

    # 关系定义
    majors: Mapped[List["MajorInfo"]] = relationship(
        "MajorInfo",
        back_populates="department",
        cascade="all, delete-orphan"
    )

    # 添加反向关系
    group_types: Mapped[List["TeachingGroupType"]] = relationship(
        "TeachingGroupType",
        back_populates="department",
        cascade="all, delete-orphan"
    )
    teacher_assignments: Mapped[List["TeacherData"]] = relationship(
        "TeacherData",
        back_populates="department",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "name", "abbr"]

        return base_fields


class MajorInfo(Base):
    __tablename__ = "major_info"
    __table_args__ = (
        {
            "comment": "专业表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 319  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="专业ID"
    )
    grade: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="年级"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="专业名称"
    )
    department_id: Mapped[Optional[str]] = mapped_column(
        String(20),
        ForeignKey("department_info.id"),
        nullable=True,
        comment="所属系部ID"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备注"
    )

    # 关系定义
    department: Mapped[Optional["DepartmentInfo"]] = relationship(
        "DepartmentInfo",
        back_populates="majors",
        foreign_keys=[department_id]
    )
    students: Mapped[List["StudentInfo"]] = relationship(
        "StudentInfo",
        back_populates="major",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "name", "grade", "department_id", "remark"]

        return base_fields


class StudentInfo(Base):
    __tablename__ = "student_info"
    __table_args__ = (
        {
            "comment": "学生信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    student_id: Mapped[str] = mapped_column(
        String(20),
        primary_key=True,
        comment="学号"
    )
    user_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        ForeignKey("user_info.user_id"),
        nullable=True,
        comment="关联 user_info.user_id"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="姓名"
    )
    sex: Mapped[str] = mapped_column(
        String(2),
        nullable=False,
        comment="性别"
    )
    grade: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="年级"
    )
    class_name: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="班级名称"
    )
    major_id: Mapped[int] = mapped_column(
        Integer,
        ForeignKey("major_info.id"),
        nullable=False,
        comment="专业ID"
    )

    # 关系定义
    user_account: Mapped[Optional["UserInfo"]] = relationship(
        "UserInfo",
        foreign_keys=[user_id]
    )
    major: Mapped["MajorInfo"] = relationship(
        "MajorInfo",
        back_populates="students",
        foreign_keys=[major_id]
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["student_id", "name", "sex", "grade", "class_name", "major_id"]

        if user_role == "admin":
            return base_fields + ["user_id"]

        return base_fields


class OtherUserInfo(Base):
    __tablename__ = "other_user_info"
    __table_args__ = (
        {
            "comment": "其他用户信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    user_id: Mapped[Optional[str]] = mapped_column(
        String(50),
        ForeignKey("user_info.user_id", ondelete="SET NULL"),
        primary_key=True,
        comment="关联 user_info.user_id"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="姓名"
    )

    # 关系定义
    user_account: Mapped["UserInfo"] = relationship(
        "UserInfo",
        back_populates="other_info",
        foreign_keys=[user_id]
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["user_id", "name"]

        return base_fields


class Semester(Base):
    __tablename__ = "semester"
    __table_args__ = (
        UniqueConstraint('name', name='name'),
        {
            "comment": "学期信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 10  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="学期ID（系统自动生成）"
    )
    name: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="学期名称（如2024学年第一学期）"
    )
    syear: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="所属学年（如2024学年）"
    )
    start_date: Mapped[date] = mapped_column(
        Date,
        nullable=False,
        comment="学期开始日期"
    )
    end_date: Mapped[date] = mapped_column(
        Date,
        nullable=False,
        comment="学期结束日期"
    )
    is_current: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="0",
        comment="是否当前学期（只能有一个为TRUE）"
    )
    created_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        server_default="CURRENT_TIMESTAMP",
        comment="创建时间"
    )

    # 添加反向关系
    teaching_group_semesters: Mapped[List["TeachingGroupSemester"]] = relationship(
        "TeachingGroupSemester",
        back_populates="semester",
        cascade="all, delete-orphan"
    )
    system_roles: Mapped[List["UserSystemRole"]] = relationship(
        "UserSystemRole",
        back_populates="semester",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "name", "syear", "start_date", "end_date", "is_current"]

        if user_role == "admin":
            return base_fields + ["created_at"]

        return base_fields


class TeacherData(Base):
    __tablename__ = "teacher_data"
    __table_args__ = (
        UniqueConstraint('user_id', 'syear', 'department_id', name='uk_teacher_semester_dept', comment='确保唯一性'),
        Index('idx_teacher_semester', 'user_id', 'syear'),
        {
            "comment": "教师学期信息表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 4090  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="记录ID"
    )
    user_id: Mapped[str] = mapped_column(
        String(20),
        ForeignKey("teacher_detail.user_id"),
        nullable=False,
        comment="教师工号"
    )
    syear: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="学年"
    )
    department_id: Mapped[str] = mapped_column(
        String(20),
        ForeignKey("department_info.id"),
        nullable=False,
        comment="所属部门ID"
    )
    role: Mapped[Optional[str]] = mapped_column(
        String(20),
        nullable=True,
        comment="职称/职位"
    )
    is_primary: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="1",
        comment="是否为主要部门"
    )

    # 关系定义
    teacher: Mapped["TeacherDetail"] = relationship(
        "TeacherDetail",
        back_populates="semester_data",
        foreign_keys=[user_id]
    )
    department: Mapped["DepartmentInfo"] = relationship(
        "DepartmentInfo",
        back_populates="teacher_assignments",
        foreign_keys=[department_id]
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "user_id", "syear", "department_id", "role", "is_primary"]

        return base_fields


class TeachingGroupType(Base):
    __tablename__ = "teaching_group_type"
    __table_args__ = (
        UniqueConstraint('name', 'department_id', name='uk_name_dept', comment='确保同一部门下类型名称唯一'),
        {
            "comment": "教研组类型表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 32  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="教研组类型ID"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="教研组类型名称"
    )
    department_id: Mapped[str] = mapped_column(
        String(20),
        ForeignKey("department_info.id"),
        nullable=False,
        comment="所属部门ID"
    )

    # 关系定义
    department: Mapped["DepartmentInfo"] = relationship(
        "DepartmentInfo",
        back_populates="group_types",
        foreign_keys=[department_id]
    )
    semester_groups: Mapped[List["TeachingGroupSemester"]] = relationship(
        "TeachingGroupSemester",
        back_populates="group_type",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "name", "department_id"]

        return base_fields


class TeachingGroupSemester(Base):
    __tablename__ = "teaching_group_semester"
    __table_args__ = (
        UniqueConstraint('type_id', 'semester_id', name='uk_type_semester',
                         comment='确保一个类型一个学期只有一个教研组'),
        Index('idx_type_semester', 'type_id', 'semester_id'),
        {
            "comment": "教研组学期表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 256  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="教研组学期ID"
    )
    type_id: Mapped[int] = mapped_column(
        Integer,
        ForeignKey("teaching_group_type.id"),
        nullable=False,
        comment="教研组类型ID"
    )
    semester_id: Mapped[int] = mapped_column(
        Integer,
        ForeignKey("semester.id"),
        nullable=False,
        comment="学期ID"
    )

    # 关系定义
    group_type: Mapped["TeachingGroupType"] = relationship(
        "TeachingGroupType",
        back_populates="semester_groups",
        foreign_keys=[type_id]
    )
    semester: Mapped["Semester"] = relationship(
        "Semester",
        back_populates="teaching_group_semesters",
        foreign_keys=[semester_id]
    )
    members: Mapped[List["TGPMember"]] = relationship(
        "TGPMember",
        back_populates="group_semester",
        cascade="all, delete-orphan"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "type_id", "semester_id"]

        return base_fields


class TGPMember(Base):
    __tablename__ = "tgp_member"
    __table_args__ = (
        UniqueConstraint('tgp_semester_id', 'user_id', name='uk_member', comment='确保一个教研组中教师不重复'),
        {
            "comment": "教研组成员表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci",
            "mysql_auto_increment": 3770  # 自增起始值
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="成员ID"
    )
    tgp_semester_id: Mapped[int] = mapped_column(
        Integer,
        ForeignKey("teaching_group_semester.id"),
        nullable=False,
        comment="教研组学期ID"
    )
    user_id: Mapped[str] = mapped_column(
        String(50),
        ForeignKey("teacher_detail.user_id"),
        nullable=False,
        comment="关联 teacher_detail.user_id"
    )
    is_leader: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="0",
        comment="是否组长"
    )
    teacher_type: Mapped[Optional[str]] = mapped_column(
        Enum('在编专任教师', '行政兼课教师(系部主任除外)', '未兼课系部教师', '未兼课行政教师', '代课教师'),
        nullable=True,
        comment="教师类型"
    )
    remark: Mapped[Optional[str]] = mapped_column(
        String(255),
        nullable=True,
        comment="备注"
    )

    # 关系定义
    group_semester: Mapped["TeachingGroupSemester"] = relationship(
        "TeachingGroupSemester",
        back_populates="members",
        foreign_keys=[tgp_semester_id]
    )
    teacher: Mapped["TeacherDetail"] = relationship(
        "TeacherDetail",
        back_populates="tgp_members",
        foreign_keys=[user_id]
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "user_id", "teacher_type", "tgp_semester_id", "is_leader", "remark"]

        return base_fields


class SystemInfo(Base):
    __tablename__ = "system_info"
    __table_args__ = (
        {
            "comment": "系统注册表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    system_code: Mapped[str] = mapped_column(
        String(20),
        primary_key=True,
        comment="系统编码（如chengji）"
    )
    name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="系统名称（如成绩系统）"
    )
    description: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="描述"
    )
    status: Mapped[str] = mapped_column(
        Enum('active', 'inactive',
             name='system_status',
             create_constraint=True,
             validate_strings=True),
        server_default="active",
        comment="状态"
    )

    # 关系定义
    roles: Mapped[List["SystemRole"]] = relationship(
        "SystemRole",
        back_populates="system",
        cascade="all, delete-orphan"
    )

    # 修正后的关系定义
    user_roles: Mapped[List["UserSystemRole"]] = relationship(
        "UserSystemRole",
        primaryjoin="SystemInfo.system_code == UserSystemRole.system_code",
        foreign_keys="[UserSystemRole.system_code]",
        back_populates="system_info",
        cascade="all, delete-orphan",
        overlaps="user_roles"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["system_code", "name", "description"]

        if user_role == "admin":
            return base_fields + ["status", "roles"]

        return base_fields


class SystemRole(Base):
    __tablename__ = "system_role"
    __table_args__ = (
        PrimaryKeyConstraint('system_code', 'role_code', name='pk_system_role'),
        ForeignKeyConstraint(
            ['system_code'],
            ['system_info.system_code'],
            ondelete='CASCADE',
            name='system_role_ibfk_1'
        ),
        {
            "comment": "系统角色表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    system_code: Mapped[str] = mapped_column(
        String(20),
        ForeignKey("system_info.system_code"),
        nullable=False,
        comment="系统编码"
    )
    role_code: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="角色编码（如teacher）"
    )
    role_name: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="角色名称（如任课教师）"
    )
    description: Mapped[Optional[str]] = mapped_column(
        Text,
        nullable=True,
        comment="描述"
    )
    is_default: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        server_default="0",
        comment="是否默认角色"
    )

    # 关系定义
    system: Mapped["SystemInfo"] = relationship(
        "SystemInfo",
        back_populates="roles",
        foreign_keys=[system_code]
    )
    user_roles: Mapped[List["UserSystemRole"]] = relationship(
        "UserSystemRole",
        back_populates="role_info",
        cascade="all, delete-orphan",
        overlaps="user_roles"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["system_code", "role_code", "role_name", "description"]

        if user_role == "admin":
            return base_fields + ["is_default"]

        return base_fields


class UserSystemRole(Base):
    __tablename__ = "user_system_role"
    __table_args__ = (
        UniqueConstraint('user_id', 'system_code', 'semester_id', name='uk_user_system_semester', comment='确保唯一性'),
        Index('idx_user_system', 'user_id', 'system_code'),
        ForeignKeyConstraint(
            ['user_id'],
            ['user_info.user_id'],
            ondelete='CASCADE',
            name='user_system_role_ibfk_1'
        ),
        ForeignKeyConstraint(
            ['system_code', 'role_code'],
            ['system_role.system_code', 'system_role.role_code'],
            ondelete='CASCADE',
            name='user_system_role_ibfk_2'
        ),
        ForeignKeyConstraint(
            ['semester_id'],
            ['semester.id'],
            ondelete='SET NULL',
            name='user_system_role_ibfk_3'
        ),
        {
            "comment": "用户系统角色表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_0900_ai_ci"
        },
    )

    id: Mapped[int] = mapped_column(
        Integer,
        primary_key=True,
        autoincrement=True,
        comment="记录ID"
    )
    user_id: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        comment="用户ID（关联user_info.user_id）"
    )
    system_code: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="系统编码"
    )
    role_code: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        comment="角色编码"
    )
    semester_id: Mapped[Optional[int]] = mapped_column(
        Integer,
        nullable=True,
        comment="学期ID"
    )
    granted_by: Mapped[Optional[str]] = mapped_column(
        String(50),
        nullable=True,
        comment="授权人"
    )
    granted_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        server_default="CURRENT_TIMESTAMP",
        comment="授权时间"
    )
    expired_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime,
        nullable=True,
        comment="过期时间"
    )
    status: Mapped[str] = mapped_column(
        Enum('active', 'revoked',
             name='role_status',
             create_constraint=True,
             validate_strings=True),
        server_default="active",
        comment="状态"
    )

    # 关系定义
    user: Mapped["UserInfo"] = relationship(
        "UserInfo",
        back_populates="system_roles",
        foreign_keys=[user_id]
    )

    # 修正后的关系定义
    system_info: Mapped["SystemInfo"] = relationship(
        "SystemInfo",
        primaryjoin="UserSystemRole.system_code == SystemInfo.system_code",
        foreign_keys="[UserSystemRole.system_code]",
        back_populates="user_roles",
        overlaps="user_roles"
    )

    role_info: Mapped["SystemRole"] = relationship(
        "SystemRole",
        back_populates="user_roles",
        foreign_keys=[system_code, role_code],
        overlaps="system_info,user_roles"
    )

    semester: Mapped["Semester"] = relationship(
        "Semester",
        back_populates="system_roles",
        foreign_keys=[semester_id]
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = ["id", "user_id", "system_code", "role_code", "status"]

        if user_role == "admin":
            return base_fields + ["semester_id", "granted_by", "granted_at", "expired_at", "user", "system_info",
                                  "role_info", "semester"]

        return base_fields


class WorkQueue(Base):
    __tablename__ = "work_queue"
    __table_args__ = (
        {
            "comment": "工作队列表",
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
        },
    )

    file_path: Mapped[str] = mapped_column(
        String(255),
        primary_key=True,
        comment="文件路径"
    )
    submit_time: Mapped[datetime] = mapped_column(
        DateTime,
        default=datetime.now,
        comment="提交时间"
    )
    which_ai: Mapped[int] = mapped_column(
        Integer,
        comment="AI类型标识"
    )

    @classmethod
    def allow_fields(cls, user_role=None):
        """定义前端可访问字段"""
        base_fields = [
            "file_path",
            "submit_time",
            "which_ai",
        ]
        if user_role == "admin":
            return base_fields + [
                # 主键字段通常对管理员可见
            ]
        return base_fields

    def __repr__(self):
        return f"<WorkQueue(file_path='{self.file_path}', submit_time={self.submit_time}, which_ai={self.which_ai})>"
